'''
python的win2扩展。
(save as 'Bonus_Excel_Win32com.py')
'''
# http://sourceforge.net/projects/pywin32/
# -*- coding: utf-8 -*-
import os
import shutil
import win32com.client
import time
import datetime
import ukeasbj_mysql_python
#---------------------------------------------------------------------------
class easyExcel:
    '''
    Some convenience methods for Excel documents accessed
    through COM.
    '''
    def __init__(self, filename=None):
        '''
        Create a new application
        if filename is None, create a new file
        else, open an exsiting one
        '''
        self.xlApp = win32com.client.Dispatch('Excel.Application')
        self.xlApp.Visible = False
        if filename:
            self.filename = filename  
            self.xlBook = self.xlApp.Workbooks.Open(filename)  
        else:  
            self.xlBook = self.xlApp.Workbooks.Add()  
            self.filename = ''
            
    def visible(self,visible = True):
        '''
        if Visible is true, the applicaion is visible
        '''
        self.Visible = visible

    def save(self, newfilename=None):
        '''
        if filename is None, save the openning file
        else save as another file used the given name
        '''
        if newfilename:  
            self.filename = newfilename
            os.remove(newfilename)
            self.xlBook.SaveAs(newfilename)  
        else:  
            self.xlBook.Save()      

    def close(self):
        '''
        Close the application
        '''
        self.xlBook.Close(SaveChanges=0)  
        del self.xlApp  

    def getCell(self, sheet, row, col):  
        '''
        Get value of one cell
        '''
        sht = self.xlBook.Worksheets(sheet)  
        return sht.Cells(row, col).Value  

    def setCell(self, sheet, row, col, value):  
        '''
        Set value of one cell
        '''
        sht = self.xlBook.Worksheets(sheet)  
        sht.Cells(row, col).Value = value 

    def getRange(self,sheet,row1,col1,row2,col2):
        '''
        Return a 2d array (i.e. tuple of tuples)
        '''
        sht = self.xlBook.Worksheets(sheet)
        return sht.Range(sht.Cells(row1,col1),sht.Cells(row2,col2)).Value

    def setRange(self,sheet,topRow,leftCol,data):
        '''
        Insert a 2d array starting at given location.
        i.e. [['a','b','c'],['a','b','c'],['a','b','c']]
        Works out the size needed for itself
        '''
        bottomRow = topRow + len(data) - 1
        rightCol = leftCol + len(data[0]) - 1
        sht = self.xlBook.Worksheets(sheet)
        data = str(data)
        try:
            sht.Range(sht.Cells(topRow, leftCol), sht.Cells(bottomRow, rightCol)).Value = data
        except:
            input("stop")

    def Filling_Table(self, sSql, MyReturn_Filling_Table_Lines,
                      sheet, My_Table_Row, My_Table_Col):
        '''
将奖金表的分项子表插入到excel文件
'''        
        b = ukeasbj_mysql_python.ukeasbj_mysql()
        data = b.Bonus_SubTable(sSql)
        self.setRange(sheet, My_Table_Row, My_Table_Col, data)
        
class bonus_101217:
    def __init__(self, data):
        # model_bonus = "E:\CVS验证\OpenERP_p\src4\SAccount运行目录\html\顾问、文案奖金申报表加密101217.xls"
        model_bonus = "C_W_bonus101217.xls"
        origdir = os.getcwd()
        # print(origdir)
        os.chdir(os.pardir)
        os.chdir(os.pardir)
        name2save = os.path.abspath('.') + '\\Model_Bonus\\' + data + '.xls'
        # print(name2save)
        # input("stop")
        # name2save = "E:\CVS验证\OpenERP_p\src4\Model_Bonus\temp.xls"
        os.chdir(origdir)
        shutil.copy (model_bonus, name2save)
        e = easyExcel(name2save)
        e.setCell(1, 5, 3, e.getCell(1, 5, 3) + "t")
        e.setCell(1, 6, 4, "10000")
        e.setCell(1, 7, 4, "12000")
        table = e.getRange( 1, 5, 3, 7, 4)
        print("2次单元赋值，显示区域数值", table)

        table2 = ( ( 'a', 'b', '2000'), ( 'a', 'b', '3000'))
        e.setRange(1, 6, 9, table2)

        try:
            e.save()
        except:
            e.close()
        e.close()

class bonus_130926:
    def __init__(self, data):
        # model_bonus = "E:\CVS验证\OpenERP_p\src4\SAccount运行目录\html\顾问、文案奖金申报表加密101217.xls"
        model_bonus = "C_bonus130926.xls"
        origdir = os.getcwd()
        # print(origdir)
        os.chdir(os.pardir)
        os.chdir(os.pardir)
        name2save = os.path.abspath('.') + '\\Model_Bonus\\' + data + '.xls'
        # print(name2save)
        # input("stop")
        # name2save = "E:\CVS验证\OpenERP_p\src4\Model_Bonus\temp.xls"
        os.chdir(origdir)
        shutil.copy (model_bonus, name2save)
        e = easyExcel(name2save)

        # e.setCell(1, 20, 3, e.getCell(1, 20, 3) + "t")
        # e.setCell(1, 21, 4, "10000")
        # e.setCell(1, 22, 4, "12000")
        # table = e.getRange( 1, 21, 4, 22, 4)
        # print("2次单元赋值，显示区域数值", table)

        # table2 = ( ( 'a', 'b', '2000'), ( 'a', 'b', '3000'))
        # e.setRange(1, 21, 7, table2)

        date_start = datetime.date(2012, 12, 25)
        date_end = datetime.date(2012, 12, 31)
        sSql  = "SELECT Series_No, Student_Type, First_Payment FROM Contract "
        sSql += "WHERE Date_First_Payment between '" + date_start.strftime("%y-%m-%d") + "' AND '"
        sSql += date_end.strftime("%y-%m-%d") + "' "

        
        e.Filling_Table(sSql, 1, 1, 21, 7)
        
        try:
            e.save()
        except:
            e.close()
        e.close()


class bonus_110315:
    def __init__(self, data):
        # model_bonus = "E:\CVS验证\OpenERP_p\src4\SAccount运行目录\html\顾问、文案奖金申报表加密101217.xls"
        model_bonus = "W_110315.xls"
        origdir = os.getcwd()
        # print(origdir)
        os.chdir(os.pardir)
        os.chdir(os.pardir)
        name2save = os.path.abspath('.') + '\\Model_Bonus\\' + data + '.xls'
        # print(name2save)
        # input("stop")
        # name2save = "E:\CVS验证\OpenERP_p\src4\Model_Bonus\temp.xls"
        os.chdir(origdir)
        shutil.copy (model_bonus, name2save)
        e = easyExcel(name2save)
        e.setCell(1, 20, 3, e.getCell(1, 5, 3) + "t")
        e.setCell(1, 21, 3, "10000")
        e.setCell(1, 22, 3, "12000")
        table = e.getRange( 1, 5, 3, 7, 4)
        print("2次单元赋值，显示区域数值", table)

        table2 = ( ( 'a', 'b', '2000'), ( 'a', 'b', '3000'))
        e.setRange(1, 1, 21, 7, table2)

        try:
            e.save()
        except:
            e.close()
        e.close()
    

'''
bonus_101217 类的测试主程序
'''
def main():
    ISOTIMEFORMAT='%Y-%m-%d %X'
    print("start time:", time.strftime( ISOTIMEFORMAT, time.localtime() ))
    # b = bonus_101217("测试101217")
    b = bonus_130926("测试130926")
    # b = bonus_110315("测试110315")
    
    print("stop time:", time.strftime( ISOTIMEFORMAT, time.localtime() ))

if __name__=="__main__":
    main()


